package dbmanager;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import framework.Ingredient;
import framework.Recipe;

public class RecipeManager extends DatabaseObjectManager {
	public static Recipe getRecipe(int id) throws SQLException {
		ResultSet srs = dbm.sendQuery("select * from recipe where id=" + id);
		srs.next();
		return new Recipe(srs);
	}

	public static ArrayList<Recipe> fetchRecipesSuitingPreferences(String name,
			boolean viewApprovedOnly) throws SQLException {
		ResultSet srs = dbm
				.sendQuery("select * from recipe where id NOT IN (select distinct "
						+ "recipe_id from recipe_uses_food_item where food_item_name "
						+ "IN (select food_item_name from pref join food_pref_about_food_item "
						+ "ON pref.id = food_pref_about_food_item.food_pref_id where is_liked=0 "
						+ "AND user_id = "
						+ AccountManager.getLogin().getId()
						+ "))");

		ArrayList<Recipe> recipeList = new ArrayList<Recipe>();
		while (srs.next()) {
			recipeList.add(new Recipe(srs));
		}

		return recipeList;
	}

	public static ArrayList<Recipe> fetchRecipes(String recipeName,
			boolean viewApprovedOnly) throws SQLException {
		String[] ingredientNames = { "" };
		return fetchRecipes(recipeName, ingredientNames, viewApprovedOnly);
	}

	// ingredientNames MUST BE AT LEAST LENGTH 1
	public static ArrayList<Recipe> fetchRecipes(String recipeName,
			String[] ingredientNames, boolean viewApprovedOnly)
			throws SQLException {
		String query = "(select * from recipe where title like '%" + recipeName
				+ "%'";

		if (viewApprovedOnly) {
			query += " and approved=1";
		}
		query += ") a";

		if (ingredientNames[0] == "") {

			query = "select * from recipe where title like '%" + recipeName
			+ "%'";
			if (viewApprovedOnly) {
				query += " and approved=1";
			}

		} else {

			query = "select distinct id, creation_date, title, steps, approved from " + query
					+ " join recipe_uses_food_item on "
					+ "a.id=recipe_uses_food_item.recipe_id "
					+ "where food_item_name like '%" + ingredientNames[0]
					+ "%'";
			for (int i = 1; i < ingredientNames.length; i++) {
				query += " or food_item_name like '%" + ingredientNames[i]
						+ "%'";
			}

		}
		
		
		ResultSet srs = dbm.sendQuery(query);
		ArrayList<Recipe> recipeList = new ArrayList<Recipe>();
		while (srs.next()) {
			recipeList.add(new Recipe(srs));
		}

		return recipeList;
	}

	/**
	 * This will create a new recipe. The recipe will create other ingredients
	 * and utensils too if they do not already exist.
	 * 
	 * @param r
	 * @throws SQLException
	 */
	public static void submitRecipe(Recipe r) throws SQLException {
		// "insert into recipe(title,steps) values ('" + r.getTitle()
		// + "','" + r.getSteps() + "')";
		for (int i = 0; i < r.getIngredients().size(); i++) {
			// add ingredients to the database if they are not already there
			FoodManager.addFoodItem(r.getIngredients().get(i).getFoodItem());
		}

		for (int i = 0; i < r.getUtensils().size(); i++) {
			// add utensils to the database if they are not already there
			UtensilManager.addUtensil(r.getUtensils().get(i));
		}

		dbm.sendInsertQuery("insert into recipe(title,steps,approved) values ('"
				+ r.getTitle() + "','" + r.getSteps() + "',0)");

		// Get the id for the recipe which was just submitted. This should be
		// the
		// recipe with the largest ID with this name.
		ResultSet rs = dbm.sendQuery("select max(id) from recipe where title='"
				+ r.getTitle() + "'");
		rs.next();
		int recipeID = Integer.parseInt(rs.getString("max(id)"));

		// Insert all ingredients into the recipe_uses_food_item table
		for (int i = 0; i < r.getIngredients().size(); i++) {
			Ingredient ing = r.getIngredients().get(i);
			dbm
					.sendInsertQuery("insert into recipe_uses_food_item"
							+ "(recipe_id,food_item_name,quantity,measuring_unit) values ('"
							+ recipeID + "','" + ing.getFoodItem().getName()
							+ "','" + ing.getQuantity() + "','"
							+ ing.getMeasuringUnit() + "')");
		}

		// Insert all utensils into the recipe_uses_utensil table
		for (int i = 0; i < r.getUtensils().size(); i++) {
			dbm
					.sendInsertQuery("insert into recipe_uses_utensil(recipe_id,utensil_name) values ('"
							+ recipeID
							+ "','"
							+ r.getUtensils().get(i).getName() + "')");
		}
	}

	public static void approveRecipe(Recipe r) throws SQLException {
		dbm.sendInsertQuery("update recipe set approved=1 where id="
				+ r.getId());
	}

	// For testing purposes only
	public static void main(String[] args) throws SQLException {
		// DatabaseObjectManager.setLogin("hannibal");
		DatabaseObjectManager.connect();

		ArrayList<Recipe> recipes = RecipeManager.fetchRecipes("", true);
		for (int i = 0; i < recipes.size(); i++) {
			System.out
					.println(recipes.get(i) + "-----------------------------");
		}

		DatabaseObjectManager.disconnect();
	}
}
